-- @owner: @chen
-- @date: 2025-11-27
-- @testpoint: 支持游标作为插入值-source表/target表-数据类型-数字,部分测试点合理报错

--setup
drop SEQUENCE if exists s_seq_cursor_case036;
drop table if exists t_cursor_source_case036;
drop table if exists t_cursor_target_case036;
reset behavior_compat_options;

--step1
CREATE SEQUENCE s_seq_cursor_case036 START WITH 1 INCREMENT BY 1;
CREATE TABLE t_cursor_source_case036 (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
age NUMBER,
height FLOAT
);
INSERT INTO t_cursor_source_case036 (id, name, age, height) VALUES (s_seq_cursor_case036.NEXTVAL, 'Alice', 30, 170.5);
INSERT INTO t_cursor_source_case036 (id, name, age, height) VALUES (s_seq_cursor_case036.NEXTVAL, 'Bob', 35, 180.2);
INSERT INTO t_cursor_source_case036 (id, name, age, height) VALUES (s_seq_cursor_case036.NEXTVAL, 'Charlie', 25, 165.8);

CREATE TABLE t_cursor_target_case036 (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
age NUMBER,
height FLOAT
);

--1.INSERT INTO t_cursor_target_case036 values(data.id,data.height);
declare
cursor cur is
select * from t_cursor_source_case036;
begin
for data in cur loop
INSERT INTO t_cursor_target_case036(id, height) values(data.id,data.height);
end loop;
end;
/

select * from t_cursor_target_case036 order by id;
--2.INSERT INTO t_cursor_target_case036 values data;
declare
cursor cur is
select id,height from t_cursor_source_case036;
begin
for data in cur loop
INSERT INTO t_cursor_target_case036 values data;
end loop;
end;
/

--3.insert into t_cursor_target_case036 values cur;
declare
begin
for cur in (select id,height from t_cursor_source_case036) loop
insert into t_cursor_target_case036 values cur;
commit;
end loop;
end;
/

--设置参数
set behavior_compat_options = 'allow_procedure_compile_check';
delete from t_cursor_target_case036;
--1.INSERT INTO t_cursor_target_case036 values data;
declare
cursor cur is
select id,height from t_cursor_source_case036;
begin
for data in cur loop
INSERT INTO t_cursor_target_case036(id, height) values data;
end loop;
end;
/

select * from t_cursor_target_case036 order by id;
delete from t_cursor_target_case036;

--2.insert into t_cursor_target_case036 values cur;
declare
begin
for cur in (select id,height from t_cursor_source_case036) loop
insert into t_cursor_target_case036(id, height) values cur;
end loop;
end;
/

select * from t_cursor_target_case036 order by id;
reset behavior_compat_options;
--step2:环境清理
drop table if exists t_cursor_source_case036;
drop table if exists t_cursor_target_case036;
drop SEQUENCE if exists s_seq_cursor_case036;